
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_DimSubMerchant_Insert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_DimSubMerchant_Insert]
GO


create procedure dbo.p_DimSubMerchant_Insert (
	@SubMerchantIdentifier varchar (30),
	@SubMerchantFullName nvarchar (150),
	@SubMerchantType nvarchar (50),
	@StreetNumber int,
	@StreetNumberSuffix varchar (50),
	@StreetName nvarchar (100),
	@StreetType nvarchar (100),
	@streetDirection nvarchar (100),
	@AddressType nvarchar (100),
	@AddressTypeIdentifier nvarchar (100),
	@MinorMunicipality nvarchar (100),
	@MajorMunicipality nvarchar (100),
	@GoverningDistrict nvarchar (100),
	@PostalCode nvarchar (20),
	@Country nvarchar (100),
	@GeoLatitude varchar (20),
	@GeoLongitude varchar (20),
	@GeocodingSpatial geography,
	@SubMerchantKey int output)
as
begin

	-- Disable row counting
	set nocount on

	declare @Error int,
	        @inTranCount int,
	        @Rows int

	select @Error = 0,
	       @inTranCount = @@trancount

	-- Only start our own transaction if caller has not supplied one
	if (@inTranCount = 0) begin transaction txnDimSubMerchantInsert
        else
	    save transaction txnDimSubMerchantInsert

	set @Error = @@error
	if (@Error <> 0) goto cleanup		

	insert into dbo.DimSubMerchant (SubMerchantIdentifier,
				        SubMerchantFullName,
				    	SubMerchantType,
				        StreetNumber,
				        StreetNumberSuffix,
				        StreetName,
				        StreetType,
				        StreetDirection,
				        AddressType,
				        AddressTypeIdentifier,
				        MinorMunicipality,
				        MajorMunicipality,
				        GoverningDistrict,
				        PostalCode,
				        Country,
				        GeoLatitude,
				        GeoLongitude,
				        GeocodingSpatial)
	  values (@SubMerchantIdentifier,
		  @SubMerchantFullName,
		  @SubMerchantType,
	          @StreetNumber,
		  @StreetNumberSuffix,
		  @StreetName,
		  @StreetType,
		  @StreetDirection,
		  @AddressType,
		  @AddressTypeIdentifier,
		  @MinorMunicipality,
		  @MajorMunicipality,
		  @GoverningDistrict,
		  @PostalCode,
		  @Country,
		  @GeoLatitude,
		  @GeoLongitude,
		  @GeocodingSpatial)

	select @Error = @@error,
	       @Rows = @@rowcount
	if ((@Error = 0) and
	    (@Rows = 0)) set @Error = -1
	if (@Error <> 0) goto cleanup

	set @SubMerchantKey = scope_identity()

cleanup:

	-- End transaction
	if (@@trancount > 0)
	begin

		-- On error roll back to save point or local transaction else commit
		if (@Error <> 0) rollback transaction txnDimSubMerchantInsert
		else
		if (@inTranCount = 0) commit transaction txnDimSubMerchantInsert

	end

	return @Error

end
GO

grant execute on dbo.p_DimSubMerchant_Insert  to [db_datawriter]
go
